﻿
using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;

/*

This provider works with the following schema for the table of user data.

CREATE TABLE Users
(
  PKID Guid NOT NULL PRIMARY KEY,
  Username Text (255) NOT NULL,
  ApplicationName Text (255) NOT NULL,
  Email Text (128) NOT NULL,
  Comment Text (255),
  Password Text (128) NOT NULL,
  PasswordQuestion Text (255),
  PasswordAnswer Text (255),
  IsApproved YesNo, 
  LastActivityDate DateTime,
  LastLoginDate DateTime,
  LastPasswordChangedDate DateTime,
  CreationDate DateTime, 
  IsOnLine YesNo,
  IsLockedOut YesNo,
  LastLockedOutDate DateTime,
  FailedPasswordAttemptCount Integer,
  FailedPasswordAttemptWindowStart DateTime,
  FailedPasswordAnswerAttemptCount Integer,
  FailedPasswordAnswerAttemptWindowStart DateTime
)

*/


namespace OliveLite.Core.Membership
{

    public sealed class OliveMembershipProvider : MembershipProvider
    {

        //
        // Global connection string, generated password length, generic exception message, event log info.
        //

        private int newPasswordLength = 8;
        private string eventSource = "OliveMembershipProvider";
        private string eventLog = "Application";
        private string exceptionMessage = "An exception occurred. Please check the Event Log.";
        private string connectionString;

        //
        // Used when determining encryption key values.
        //

        private MachineKeySection machineKey;

        //
        // If false, exceptions are thrown to the caller. If true,
        // exceptions are written to the event log.
        //

        private bool pWriteExceptionsToEventLog;

        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }


        //
        // System.Configuration.Provider.ProviderBase.Initialize Method
        //

        public override void Initialize(string name, NameValueCollection config)
        {
            //
            // Initialize values from web.config.
            //

            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "OliveMembershipProvider";

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Olive Membership provider");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);

            pApplicationName = GetConfigValue(config["applicationName"],
                                            System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
            pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
            pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
            pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
            pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
            pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
            pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
            pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
            pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
            pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));

            string temp_format = config["passwordFormat"];
            if (temp_format == null)
            {
                temp_format = "Hashed";
            }

            switch (temp_format)
            {
                case "Hashed":
                    pPasswordFormat = MembershipPasswordFormat.Hashed;
                    break;
                case "Encrypted":
                    pPasswordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Clear":
                    pPasswordFormat = MembershipPasswordFormat.Clear;
                    break;
                default:
                    throw new ProviderException("Password format not supported.");
            }

            //
            // Initialize Database Connection.
            //

            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = ConnectionStringSettings.ConnectionString;


            // Get encryption and decryption key information from the configuration.
            Configuration cfg =
              WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

            if (machineKey.ValidationKey.Contains("AutoGenerate"))
                if (PasswordFormat != MembershipPasswordFormat.Clear)
                    throw new ProviderException("Hashed or Encrypted passwords " +
                                                "are not supported with auto-generated keys.");
        }


        //
        // A helper function to retrieve config values from the configuration file.
        //

        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
                return defaultValue;

            return configValue;
        }


        //
        // System.Web.Security.MembershipProvider properties.
        //


        private string pApplicationName;
        private bool pEnablePasswordReset;
        private bool pEnablePasswordRetrieval;
        private bool pRequiresQuestionAndAnswer;
        private bool pRequiresUniqueEmail;
        private int pMaxInvalidPasswordAttempts;
        private int pPasswordAttemptWindow;
        private MembershipPasswordFormat pPasswordFormat;

        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override bool EnablePasswordReset
        {
            get { return pEnablePasswordReset; }
        }


        public override bool EnablePasswordRetrieval
        {
            get { return pEnablePasswordRetrieval; }
        }


        public override bool RequiresQuestionAndAnswer
        {
            get { return pRequiresQuestionAndAnswer; }
        }


        public override bool RequiresUniqueEmail
        {
            get { return pRequiresUniqueEmail; }
        }


        public override int MaxInvalidPasswordAttempts
        {
            get { return pMaxInvalidPasswordAttempts; }
        }


        public override int PasswordAttemptWindow
        {
            get { return pPasswordAttemptWindow; }
        }


        public override MembershipPasswordFormat PasswordFormat
        {
            get { return pPasswordFormat; }
        }

        private int pMinRequiredNonAlphanumericCharacters;

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return pMinRequiredNonAlphanumericCharacters; }
        }

        private int pMinRequiredPasswordLength;

        public override int MinRequiredPasswordLength
        {
            get { return pMinRequiredPasswordLength; }
        }

        private string pPasswordStrengthRegularExpression;

        public override string PasswordStrengthRegularExpression
        {
            get { return pPasswordStrengthRegularExpression; }
        }

        //
        // System.Web.Security.MembershipProvider methods.
        //

        //
        // MembershipProvider.ChangePassword
        //

        public override bool ChangePassword(string email, string oldPwd, string newPwd)
        {
            if (!ValidateUser(email, oldPwd))
                return false;


            ValidatePasswordEventArgs args =
              new ValidatePasswordEventArgs(email, newPwd, true);

            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Change password canceled due to new password validation failure.");

            MembershipUser u = GetUser(email, true);

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Core.ContactUser SET Password = @Password, LastPasswordChangedDT = @LastPasswordChangedDT, FailedPasswordAttemptCount = 0 " +
                            "WHERE Core.ContactUser.ContactId = @ProviderUserKey";
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 127).Value = EncodePassword(newPwd);
            cmd.Parameters.Add("@LastPasswordChangedDT", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;


            int rowsAffected = 0;

            try
            {
                cn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePassword");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }



        //
        // MembershipProvider.ChangePasswordQuestionAndAnswer
        //

        public override bool ChangePasswordQuestionAndAnswer(string email,
                      string password,
                      string newSecurityQuestion,
                      string newSecurityAnswer)
        {
            if (!ValidateUser(email, password))
                return false;

            MembershipUser u = GetUser(email, true);

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Core.ContactUser SET SecurityQuestion = @Question, SecurityAnswer = @Answer, " +
                            "FailedPasswordAnswerAttemptCount = 0 " + 
                            "WHERE ContactId = @ProviderUserKey";

            cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 127).Value = newSecurityQuestion;
            cmd.Parameters.Add("@Answer", SqlDbType.NVarChar, 127).Value = EncodePassword(newSecurityAnswer);
            cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;

            int rowsAffected = 0;

            try
            {
                cn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }



        //
        // MembershipProvider.CreateUser
        //

        public override MembershipUser CreateUser(string username,
                 string password,
                 string email,
                 string passwordQuestion,
                 string passwordAnswer,
                 bool isApproved,
                 object providerUserKey,
                 out MembershipCreateStatus status)
        {
            ValidatePasswordEventArgs args =
              new ValidatePasswordEventArgs(email, password, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }



            if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            MembershipUser u;
            if (username == email)
                u = GetUser(email, false);
            else
                u = GetUser(GetEmailByOpenIDUserName(username), false);

            if (u == null)
            {
                DateTime createDate = DateTime.Now;

                SqlConnection cn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Core.Contact " +
                    " (FirstName, Email, CreatedById, CreatedByName, ModifiedById, ModifiedByName) VALUES " +
                    " ('', @Email, @CreatedById, 'Self Registered', @ModifiedById, 'Self Registered')";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;
                cmd.Parameters.Add("@CreatedById", SqlDbType.UniqueIdentifier).Value = Guid.Empty;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.UniqueIdentifier).Value = Guid.Empty;

                try
                {
                    cn.Open();
                    int recAdded = cmd.ExecuteNonQuery();
                    if (recAdded > 0)
                    {
                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }
                }
                catch (Exception e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                    }
                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    cn.Close();
                }

                if (status != MembershipCreateStatus.Success) return null;

                cmd.CommandText = "SELECT convert(nvarchar(127), ContactId) FROM Core.Contact WHERE Email = @Email";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

                Guid contactid = Guid.Empty;

                try
                {
                    cn.Open();
                    string id = (string)cmd.ExecuteScalar();
                    if (id != null)
                    {
                        contactid = new Guid(id);
                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }
                }
                catch (Exception e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                    }
                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    cn.Close();
                }

                if (status != MembershipCreateStatus.Success) return null;
                if (passwordQuestion == null) passwordQuestion = "";
                if (passwordAnswer == null) passwordAnswer = "";

                cmd.CommandText = "INSERT INTO Core.ContactUser (" +
                    "ContactId, OpenIdUserName, Password, SecurityQuestion, SecurityAnswer, IsApprovedYN, Comment, CreateDT, " +
                    "LastPasswordChangedDT, LastActivityDT, IsLockedOutYN, LastLockedOutDT, FailedPasswordAttemptCount, " +
                    "FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) VALUES (" +
                    "@ContactId, @OpenIdUserName, @Password, @SecurityQuestion, @SecurityAnswer, @IsApprovedYN, @Comment, @CreateDT, " +
                    "@LastPasswordChangedDT, @LastActivityDT, @IsLockedOutYN, @LastLockedOutDT, @FailedPasswordAttemptCount, " +
                    "@FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart)";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@ContactId", SqlDbType.UniqueIdentifier).Value = contactid;
                cmd.Parameters.Add("@OpenIdUserName", SqlDbType.NVarChar, 255).Value = username;
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 127).Value = EncodePassword(password);
                cmd.Parameters.Add("@SecurityQuestion", SqlDbType.NVarChar, 127).Value = passwordQuestion;
                cmd.Parameters.Add("@SecurityAnswer", SqlDbType.NVarChar, 127).Value = EncodePassword(passwordAnswer);
                cmd.Parameters.Add("@IsApprovedYN", SqlDbType.NChar, 1).Value = isApproved ? "Y" : "N";
                cmd.Parameters.Add("@Comment", SqlDbType.NVarChar, 255).Value = "";
                cmd.Parameters.Add("@CreateDT", SqlDbType.DateTime).Value = createDate;
                cmd.Parameters.Add("@LastPasswordChangedDT", SqlDbType.DateTime).Value = createDate;
                cmd.Parameters.Add("@LastActivityDT", SqlDbType.DateTime).Value = createDate;
                cmd.Parameters.Add("@IsLockedOutYN", SqlDbType.NChar, 1).Value = "N";
                cmd.Parameters.Add("@LastLockedOutDT", SqlDbType.DateTime).Value = createDate;
                cmd.Parameters.Add("@FailedPasswordAttemptCount", SqlDbType.Int).Value = 0;
                cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", SqlDbType.DateTime).Value = createDate;
                cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", SqlDbType.Int).Value = 0;
                cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", SqlDbType.DateTime).Value = createDate;

                SqlCommand updateCmd = new SqlCommand();
                updateCmd.Connection = cn;
                updateCmd.CommandType = CommandType.Text;
                updateCmd.CommandText = "UPDATE Core.Contact SET " +
                    " CreatedById = @ContactId, ModifiedById = @ContactId " +
                    " WHERE ContactId = @ContactId ";
                updateCmd.Parameters.Add("@ContactId", SqlDbType.UniqueIdentifier).Value = contactid;

                try
                {
                    cn.Open();
                    int rowsAffected = 0;
                    rowsAffected += cmd.ExecuteNonQuery();
                    rowsAffected += updateCmd.ExecuteNonQuery();
                    if (rowsAffected > 1)
                    {
                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }                    
                }
                catch (Exception e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                    }
                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    cn.Close();
                }


                return GetUser(email, false);
            }
            else
            {
                if (!u.IsApproved)
                {
                    if (passwordQuestion == null) passwordQuestion = "";
                    if (passwordAnswer == null) passwordAnswer = "";
                    SqlConnection cn = new SqlConnection(connectionString);
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "UPDATE Core.ContactUser SET SecurityQuestion = @Question, SecurityAnswer = @Answer, " +
                                    "Password = @Password, IsApprovedYN = 'Y', IsLockedOutYN = 'N' " +
                                    "WHERE ContactId = @ProviderUserKey";

                    cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 127).Value = passwordQuestion;
                    cmd.Parameters.Add("@Answer", SqlDbType.NVarChar, 127).Value = EncodePassword(passwordAnswer);
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 127).Value = EncodePassword(password);
                    cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;
                    int rowsAffected = 0;

                    try
                    {
                        cn.Open();
                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "CreateUser");

                            throw new ProviderException(exceptionMessage);
                        }
                        else
                        {
                            throw e;
                        }
                    }
                    finally
                    {
                        cn.Close();
                    }
                    status = MembershipCreateStatus.Success;
                }
                else
                {
                    status = MembershipCreateStatus.DuplicateUserName;
                }
            }


            return null;
        }



        //
        // MembershipProvider.DeleteUser
        //

        public override bool DeleteUser(string email, bool deleteAllRelatedData)
        {
            MembershipUser u = GetUser(email, false);

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Core.ContactUser SET Core.ContactUser.IsDroppedYN = 'Y' WHERE Core.ContactUser.ContactId = @ProviderUserKey";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;

            int rowsAffected = 0;

            try
            {
                cn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
                if (deleteAllRelatedData)
                {
                    // Process commands to delete all data for the user in the database.
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "DeleteUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (rowsAffected > 0)
                return true;

            return false;
        }



        //
        // MembershipProvider.GetAllUsers
        //

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Count(*) FROM Core.ContactUser WHERE IsDroppedYN = 'N'";

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader reader = null;
            totalRecords = 0;

            try
            {
                cn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0) { return users; }

                cmd.CommandText = "SELECT Core.ContactUser.ContactId, OpenIDUserName, Core.Contact.Email, SecurityQuestion," +
                         " Comment, IsApprovedYN, IsLockedOutYN, Core.ContactUser.CreateDT, LastLoginDT," +
                         " LastActivityDT, LastPasswordChangedDT, LastLockedOutDT " +
                         " FROM Core.ContactUser, Core.Contact " +
                         " WHERE Core.ContactUser.IsDroppedYN = 'N' AND Core.ContactUser.ContactId = Core.Contact.ContactId " +
                         " ORDER BY Core.Contact.Email Asc";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex) { cmd.Cancel(); }

                    counter++;
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllUsers ");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }

            return users;
        }


        //
        // MembershipProvider.GetNumberOfUsersOnline
        //

        public override int GetNumberOfUsersOnline()
        {

            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Count(*) FROM Core.ContactUser, Core.Contact WHERE Core.ContactUser.ContactId = Core.Contact.ContactId " +
                "AND Core.ContactUser.IsDroppedYN = 'N' AND Core.ContactUser.LastActivityDT > @CompareDate";
            cmd.Parameters.Add("@CompareDate", SqlDbType.DateTime).Value = compareTime;

            int numOnline = 0;

            try
            {
                cn.Open();
                numOnline = (int)cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            return numOnline;
        }



        //
        // MembershipProvider.GetPassword
        //

        public override string GetPassword(string email, string answer)
        {
            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("Password Retrieval Not Enabled.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                throw new ProviderException("Cannot retrieve Hashed passwords.");
            }

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Password, PasswordAnswer, IsLockedOutYN FROM Core.ContactUser, Core.Contact " +
                "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.Email = @Email";
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

            string password = "";
            string passwordAnswer = "";
            SqlDataReader reader = null;

            try
            {
                cn.Open();

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.HasRows)
                {
                    reader.Read();
                    if (reader.GetString(2) == "Y")
                        throw new MembershipPasswordException("The supplied user is locked out.");

                    password = reader.GetString(0);
                    passwordAnswer = reader.GetString(1);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user name is not found.");
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetPassword");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }


            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
            {
                UpdateFailureCount(email, "passwordAnswer");

                throw new MembershipPasswordException("Incorrect password answer.");
            }


            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
            {
                password = UnEncodePassword(password);
            }

            return password;
        }



        //
        // MembershipProvider.GetUser(string, bool)
        //

        public override MembershipUser GetUser(string email, bool userIsOnline)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Core.ContactUser.ContactId, OpenIDUserName, Core.Contact.Email, SecurityQuestion," +
                     " Comment, IsApprovedYN, IsLockedOutYN, Core.ContactUser.CreateDT, LastLoginDT," +
                     " LastActivityDT, LastPasswordChangedDT, LastLockedOutDT " +
                     " FROM Core.ContactUser, Core.Contact " +
                     " WHERE Core.ContactUser.IsDroppedYN = 'N' AND Core.ContactUser.ContactId = Core.Contact.ContactId " +
                     " AND (Core.Contact.Email = @Email or Core.ContactUser.OpenIDUserName = @Email)" +
                     " ORDER BY Core.Contact.Email Asc";

            cmd.Parameters.Add("@Email", SqlDbType.NVarChar,127).Value = email;

            MembershipUser u = null;
            SqlDataReader reader = null;

            try
            {
                cn.Open();
                reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    u = GetUserFromReader(reader);
                    reader.Close();
                    reader = null;
                    if (userIsOnline)
                    {
                        SqlCommand updateCmd = new SqlCommand("UPDATE Core.ContactUser " +
                                  "SET LastActivityDT = @LastActivityDT " +
                                  "WHERE Core.ContactUser.ContactId = @ProviderUserKey", cn);

                        updateCmd.Parameters.Add("@LastActivityDT", SqlDbType.DateTime).Value = DateTime.Now;
                        updateCmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;

                        updateCmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(String, Boolean)");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }

            return u;
        }


        //
        // MembershipProvider.GetUser(object, bool)
        //

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Core.ContactUser.ContactId, OpenIDUserName, Core.Contact.Email, SecurityQuestion," +
                     " Comment, IsApprovedYN, IsLockedOutYN, Core.ContactUser.CreateDT, LastLoginDT," +
                     " LastActivityDT, LastPasswordChangedDT, LastLockedOutDT " +
                     " FROM Core.ContactUser, Core.Contact " +
                     " WHERE Core.ContactUser.IsDroppedYN = 'N' AND Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.ContactId = @ProviderUserKey" +
                     " ORDER BY Core.Contact.Email Asc";
            cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = providerUserKey;

            MembershipUser u = null;
            SqlDataReader reader = null;

            try
            {
                cn.Open();
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    u = GetUserFromReader(reader);
                    reader.Close();
                    reader = null;
                    if (userIsOnline)
                    {
                        SqlCommand updateCmd = new SqlCommand("UPDATE Core.ContactUser " +
                                  "SET LastActivityDT = @LastActivityDT " +
                                  "WHERE ContactId = @ProviderUserKey", cn);

                        updateCmd.Parameters.Add("@LastActivityDT", SqlDbType.DateTime).Value = DateTime.Now;
                        updateCmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = providerUserKey;

                        updateCmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(Object, Boolean)");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }

            return u;
        }


        //
        // GetUserFromReader
        //    A helper function that takes the current row from the SqlDataReader
        // and hydrates a MembershipUser from the values. Called by the 
        // MembershipUser.GetUser implementation.
        //

        private MembershipUser GetUserFromReader(SqlDataReader reader)
        {
            object providerUserKey = reader.GetValue(0);
            string username = reader.GetString(1);
            string email = reader.GetString(2);

            string passwordQuestion = "";
            if (reader.GetValue(3) != DBNull.Value)
                passwordQuestion = reader.GetString(3);

            string comment = "";
            if (reader.GetValue(4) != DBNull.Value)
                comment = reader.GetString(4);

            bool isApproved = (reader.GetString(5) == "Y");
            bool isLockedOut = (reader.GetString(6) == "Y");
            DateTime creationDate = reader.GetDateTime(7);

            DateTime lastLoginDate = new DateTime();
            if (reader.GetValue(8) != DBNull.Value)
                lastLoginDate = reader.GetDateTime(8);

            DateTime lastActivityDate = reader.GetDateTime(9);
            DateTime lastPasswordChangedDate = reader.GetDateTime(10);

            DateTime lastLockedOutDate = new DateTime();
            if (reader.GetValue(11) != DBNull.Value)
                lastLockedOutDate = reader.GetDateTime(11);

            MembershipUser u = new MembershipUser(this.Name,
                                                  username,
                                                  providerUserKey,
                                                  email,
                                                  passwordQuestion,
                                                  comment,
                                                  isApproved,
                                                  isLockedOut,
                                                  creationDate,
                                                  lastLoginDate,
                                                  lastActivityDate,
                                                  lastPasswordChangedDate,
                                                  lastLockedOutDate);

            return u;
        }


        //
        // MembershipProvider.UnlockUser
        //

        public override bool UnlockUser(string email)
        {
            MembershipUser u = GetUser(email, false);
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Core.ContactUser SET IsLockedOutYN = 'N', LastLockedOutDT = @LastLockedOutDT " +
                "WHERE Core.ContactUser.ContactId = @ProviderUserKey";
            cmd.Parameters.Add("@LastLockedOutDT", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = u.ProviderUserKey;

            int rowsAffected = 0;

            try
            {
                cn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UnlockUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (rowsAffected > 0)
                return true;

            return false;
        }


        //
        // MembershipProvider.GetUserNameByEmail
        //

        public override string GetUserNameByEmail(string email)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Core.Contact.Email FROM Core.ContactUser, Core.Contact " +
                "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND " +
                "Core.Contact.Email = @Email and Core.ContactUser.IsApprovedYN = 'Y'";
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

            string username = "";

            try
            {
                cn.Open();
                username = (string)cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUserNameByEmail");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (username == null)
                username = "";

            return username;
        }




        //
        // MembershipProvider.ResetPassword
        //

        public override string ResetPassword(string email, string answer)
        {
            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password reset is not enabled.");
            }

            if (answer == null && RequiresQuestionAndAnswer)
            {
                UpdateFailureCount(email, "passwordAnswer");

                throw new ProviderException("Password answer required for password reset.");
            }

            string newPassword =
              System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);


            ValidatePasswordEventArgs args =
              new ValidatePasswordEventArgs(email, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT SecurityAnswer, IsLockedOutYN, Core.ContactUser.ContactId FROM Core.ContactUser, Core.Contact " +
                "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.Email = @Email";
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

            int rowsAffected = 0;
            string passwordAnswer = "";
            SqlDataReader reader = null;
            Guid providerUserKey = Guid.Empty;
            try
            {
                cn.Open();
                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    if (reader.GetString(1) == "Y")
                        throw new MembershipPasswordException("The supplied user is locked out.");

                    passwordAnswer = reader.GetString(0);
                    providerUserKey = reader.GetGuid(2);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user name is not found.");
                }

                if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
                {
                    UpdateFailureCount(email, "passwordAnswer");

                    throw new MembershipPasswordException("Incorrect password answer.");
                }
                reader.Close();
                reader = null;
                SqlCommand updateCmd = new SqlCommand("UPDATE Core.ContactUser " +
                    " SET Password = @Password, LastPasswordChangedDT = @LastPasswordChangedDT, FailedPasswordAttemptCount = 0 " +
                    " WHERE Core.ContactUser.ContactId = @ProviderUserKey AND IsLockedOutYN = 'N'", cn);

                updateCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 127).Value = EncodePassword(newPassword);
                updateCmd.Parameters.Add("@LastPasswordChangedDT", SqlDbType.DateTime).Value = DateTime.Now;
                updateCmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = providerUserKey;

                rowsAffected = updateCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ResetPassword");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }

            if (rowsAffected > 0)
            {
                return newPassword;
            }
            else
            {
                throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
            }
        }


        //
        // MembershipProvider.UpdateUser
        //

        public override void UpdateUser(MembershipUser user)
        {
            SqlConnection cn = new SqlConnection(connectionString);

            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = cn;
            cmd1.CommandType = CommandType.Text;
            cmd1.CommandText = "UPDATE Core.ContactUser SET Comment = @Comment, IsApprovedYN = @IsApprovedYN WHERE ContactId = @ProviderUserKey";
            cmd1.Parameters.Add("@Comment", SqlDbType.NVarChar, 255).Value = user.Comment;
            cmd1.Parameters.Add("@IsApprovedYN", SqlDbType.NChar,1).Value = user.IsApproved ? "Y" : "N";
            cmd1.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = user.ProviderUserKey;

            SqlCommand cmd2 = new SqlCommand();
            cmd2.Connection = cn;
            cmd2.CommandType = CommandType.Text;
            cmd2.CommandText = "UPDATE Core.Contact SET Email = @Email WHERE ContactId = @ProviderUserKey";
            cmd2.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = user.Email;
            cmd2.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = user.ProviderUserKey;

            try
            {
                cn.Open();
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }
        }


        //
        // MembershipProvider.ValidateUser
        //

        public override bool ValidateUser(string email, string password)
        {
            bool isValid = false;

            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Password, IsApprovedYN, Core.ContactUser.ContactId FROM Core.ContactUser, Core.Contact " +
                "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND " +
                "Core.Contact.Email = @Email AND IsLockedOutYN = 'N' AND Core.ContactUser.IsDroppedYN = 'N'";
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

            SqlDataReader reader = null;
            bool isApproved = false;
            string pwd = "";
            Guid providerUserKey = Guid.Empty;
            try
            {
                cn.Open();
                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    pwd = reader.GetString(0);
                    isApproved = (reader.GetString(1) == "Y");
                    providerUserKey = reader.GetGuid(2);
                }
                else
                {
                    return false;
                }

                reader.Close();

                if (CheckPassword(password, pwd))
                {
                    if (isApproved)
                    {
                        isValid = true;

                        SqlCommand updateCmd = new SqlCommand("UPDATE Core.ContactUser SET LastLoginDT = @LastLoginDT, FailedPasswordAttemptCount = 0 " +
                                                                " WHERE ContactId = @ProviderUserKey", cn);

                        updateCmd.Parameters.Add("@LastLoginDT", SqlDbType.DateTime).Value = DateTime.Now;
                        updateCmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = providerUserKey;

                        updateCmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    cn.Close();
                    UpdateFailureCount(email, "password");
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ValidateUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }

            return isValid;
        }


        //
        // UpdateFailureCount
        //   A helper method that performs the checks and updates associated with
        // password failure tracking.
        //

        private void UpdateFailureCount(string email, string failureType)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT FailedPasswordAttemptCount, " +
                                              "  FailedPasswordAttemptWindowStart, " +
                                              "  FailedPasswordAnswerAttemptCount, " +
                                              "  FailedPasswordAnswerAttemptWindowStart, " +
                                              "  Core.ContactUser.ContactId " +
                                              "  FROM Core.ContactUser, Core.Contact " +
                                              "  WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.Email = @Email", cn);

            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = email;

            SqlDataReader reader = null;
            DateTime windowStart = new DateTime();
            int failureCount = 0;
            Guid provideruserkey = Guid.Empty;

            try
            {
                cn.Open();

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();

                    if (failureType == "password")
                    {
                        failureCount = reader.GetInt32(0);
                        windowStart = reader.GetDateTime(1);
                    }

                    if (failureType == "passwordAnswer")
                    {
                        failureCount = reader.GetInt32(2);
                        windowStart = reader.GetDateTime(3);
                    }

                    provideruserkey = reader.GetGuid(4);
                }

                reader.Close();

                DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

                if (failureCount == 0 || DateTime.Now > windowEnd)
                {
                    // First password failure or outside of PasswordAttemptWindow. 
                    // Start a new password failure count from 1 and a new window starting now.

                    if (failureType == "password")
                        cmd.CommandText = "UPDATE Core.ContactUser " +
                                          "  SET FailedPasswordAttemptCount = @Count, " +
                                          "      FailedPasswordAttemptWindowStart = @WindowStart " +
                                          "  WHERE ContactId = @ProviderUserKey";

                    if (failureType == "passwordAnswer")
                        cmd.CommandText = "UPDATE Core.ContactUser " +
                                          "  SET FailedPasswordAnswerAttemptCount = @Count, " +
                                          "      FailedPasswordAnswerAttemptWindowStart = @WindowStart " +
                                          "  WHERE ContactId = @ProviderUserKey";

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("@Count", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@WindowStart", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = provideruserkey;

                    if (cmd.ExecuteNonQuery() < 0)
                        throw new ProviderException("Unable to update failure count and window start.");
                }
                else
                {
                    if (failureCount++ >= MaxInvalidPasswordAttempts)
                    {
                        // Password attempts have exceeded the failure threshold. Lock out
                        // the user.

                        cmd.CommandText = "UPDATE Core.ContactUser " +
                                          "  SET IsLockedOutYN = @IsLockedOutYN, LastLockedOutDT = @LastLockedOutDT " +
                                          "  WHERE ContactId = @ProviderUserKey";

                        cmd.Parameters.Clear();

                        cmd.Parameters.Add("@IsLockedOutYN", SqlDbType.NChar,1).Value = "Y";
                        cmd.Parameters.Add("@LastLockedOutDT", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = provideruserkey;

                        if (cmd.ExecuteNonQuery() < 0)
                            throw new ProviderException("Unable to lock out user.");
                    }
                    else
                    {
                        // Password attempts have not exceeded the failure threshold. Update
                        // the failure counts. Leave the window the same.

                        if (failureType == "password")
                            cmd.CommandText = "UPDATE Core.ContactUser " +
                                              "  SET FailedPasswordAttemptCount = @Count" +
                                              "  WHERE ContactId = @ProviderUserKey";

                        if (failureType == "passwordAnswer")
                            cmd.CommandText = "UPDATE Users " +
                                              "  SET FailedPasswordAnswerAttemptCount = @Count" +
                                              "  WHERE ContactId = @ProviderUserKey";

                        cmd.Parameters.Clear();

                        cmd.Parameters.Add("@Count", SqlDbType.Int).Value = failureCount;
                        cmd.Parameters.Add("@ProviderUserKey", SqlDbType.UniqueIdentifier).Value = provideruserkey;

                        if (cmd.ExecuteNonQuery() < 0)
                            throw new ProviderException("Unable to update failure count.");
                    }
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateFailureCount");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                cn.Close();
            }
        }


        //
        // CheckPassword
        //   Compares password values based on the MembershipPasswordFormat.
        //

        private bool CheckPassword(string password, string dbpassword)
        {
            string pass1 = password;
            string pass2 = dbpassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Encrypted:
                    pass2 = UnEncodePassword(dbpassword);
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password);
                    break;
                default:
                    break;
            }

            if (pass1 == pass2)
            {
                return true;
            }

            return false;
        }


        //
        // EncodePassword
        //   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
        //

        private string EncodePassword(string password)
        {
            string encodedPassword = password;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                      Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = HexToByte(machineKey.ValidationKey);
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return encodedPassword;
        }


        //
        // UnEncodePassword
        //   Decrypts or leaves the password clear based on the PasswordFormat.
        //

        private string UnEncodePassword(string encodedPassword)
        {
            string password = encodedPassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                      Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Cannot unencode a hashed password.");
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return password;
        }

        //
        // HexToByte
        //   Converts a hexadecimal string to a byte array. Used to convert encryption
        // key values from the configuration.
        //

        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }


        //
        // MembershipProvider.FindUsersByName
        //

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {

            return FindUsersByEmail(usernameToMatch, pageIndex, pageSize, out totalRecords);
        }

        //
        // MembershipProvider.FindUsersByEmail
        //

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Core.ContactUser, Core.Contact " +
                                              "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.Email LIKE @Email AND Core.ContactUser.IsDroppedYN = 'N'", cn);
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127).Value = "%" + emailToMatch.Trim() + "%";

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader reader = null;
            totalRecords = 0;

            try
            {
                cn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0) { return users; }

                cmd.CommandText = "SELECT Core.ContactUser.ContactId, OpenIDUserName, Core.Contact.Email, SecurityQuestion," +
                         " Comment, IsApprovedYN, IsLockedOutYN, Core.ContactUser.CreateDT, LastLoginDT," +
                         " LastActivityDT, LastPasswordChangedDT, LastLockedOutDT " +
                         " FROM Core.ContactUser, Core.Contact " +
                         " WHERE Core.ContactUser.IsDroppedYN = 'N' AND Core.ContactUser.ContactId = Core.Contact.ContactId AND Core.Contact.Email LIKE @Email" +
                         " ORDER BY Core.Contact.Email Asc";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex) { cmd.Cancel(); }

                    counter++;
                }
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByEmail");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                cn.Close();
            }

            return users;
        }


        //
        // WriteToEventLog
        //   A helper function that writes exception detail to the event log. Exceptions
        // are written to the event log as a security measure to avoid private database
        // details from being returned to the browser. If a method does not return a status
        // or boolean indicating the action succeeded or failed, a generic exception is also 
        // thrown by the caller.
        //

        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = "An exception occurred communicating with the data source.\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }

        private string GetEmailByOpenIDUserName(string openIDUserName)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            SqlParameter p;
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT @Email = Email FROM Core.ContactUser, Core.Contact " +
                "WHERE Core.ContactUser.ContactId = Core.Contact.ContactId AND OpenIDUserName = @OpenIDUserName";
            cmd.Parameters.Clear();
            p = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 127);
            p.Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@OpenIDUserName", SqlDbType.NVarChar, 255).Value = openIDUserName;

            int rowsAffected = 0;

            try
            {
                cn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetEmailByOpenIDUserName");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                cn.Close();
            }

            if (rowsAffected > 0)
            {
                return p.Value.ToString();
            }

            return "";
        }
    }
}
